﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace FYPDemo
{
    public class ProductManagement
    {
        public static void cartManagement()
        {
            string[] orderIDList;
            string productID = "";
            int quantity = 0;
            int orderIDLength = 0;
            int i = 0;
            DataTable dt = new DataTable();
            DataTable dt1 = new DataTable();

            dt = listExpiredCart();
            orderIDLength = dt.Rows.Count;
            orderIDList = new string[orderIDLength];

            foreach (DataRow dtRows in dt.Rows)
            {
                orderIDList[i] = dtRows[0].ToString();

                i++;
            }

            for (int j = 0; j < orderIDLength; j++)
            {
                dt1 = listExpiredCartDetails(orderIDList[j]);

                foreach (DataRow dtRows1 in dt1.Rows)
                {
                    productID = dtRows1[0].ToString();
                    quantity = Convert.ToInt32(dtRows1[1]);
                    releaseHoldedProduct(productID, quantity);
                }

                //method to update order status to cancel ->)
                autoCancelCart(orderIDList[j]);
            }

        }

        public static DataTable listExpiredCart()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listExpiredCart";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable listExpiredCartDetails(string orderID)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listExpiredCartDetails";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.VarChar, 20));
                cmd.Parameters["@OrderID"].Value = orderID;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static void releaseHoldedProduct(string productID, int qty)
        {
            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "releaseHoldProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 10));
                cmd.Parameters["@ProductID"].Value = productID;

                cmd.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
                cmd.Parameters["@Quantity"].Value = qty;

                cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }
        }

        public static DataTable getLatestCart(string name)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "getLatestCart";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@MemberName", SqlDbType.VarChar, 50));
                cmd.Parameters["@MemberName"].Value = name;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static int checkOutCart(string orderID)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "checkoutOrder";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.VarChar, 20));
                cmd.Parameters["@OrderID"].Value = orderID;

                result = cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return result;
        }

        public static void autoCancelCart(string orderID)
        {
            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "autoCancelCart";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.VarChar, 20));
                cmd.Parameters["@OrderID"].Value = orderID;

                cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }
        }

        public static DataTable displayProduct(string category, string memberName)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "displayProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Category", SqlDbType.VarChar, 30));
                cmd.Parameters["@Category"].Value = category;

                cmd.Parameters.Add(new SqlParameter("@Vendor", SqlDbType.VarChar, 50));
                cmd.Parameters["@Vendor"].Value = memberName;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return dt;
        }        

        public static string getLatestOrderID()
        {
            string orderID = "";
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "getLatestOrderID";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    orderID = dt.Rows[0][0].ToString();
                }                
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return orderID;
        }

        public static void insertOrder(string orderID, string name)
        {
            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "insertOrder";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@orderID", SqlDbType.VarChar, 20));
                cmd.Parameters["@orderID"].Value = orderID;

                cmd.Parameters.Add(new SqlParameter("@memberName", SqlDbType.VarChar, 50));
                cmd.Parameters["@memberName"].Value = name;

                cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }
        }


        public static void insertOrderDetails(string orderID, string productID, int quantity, double price)
        {

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "insertOrderDetails";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@orderID", SqlDbType.VarChar, 20));
                cmd.Parameters["@orderID"].Value = orderID;

                cmd.Parameters.Add(new SqlParameter("@productID", SqlDbType.VarChar, 10));
                cmd.Parameters["@productID"].Value = productID;

                cmd.Parameters.Add(new SqlParameter("@quantity", SqlDbType.Int));
                cmd.Parameters["@quantity"].Value = quantity;

                cmd.Parameters.Add(new SqlParameter("@price", SqlDbType.Decimal));
                cmd.Parameters["@price"].Value = price;

                cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }
        }

        public static int checkRedundantProduct(string orderID, string prodID)
        {
            int result = 0;
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "checkRedundantProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.VarChar, 20));
                cmd.Parameters["@OrderID"].Value = orderID;

                cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 10));
                cmd.Parameters["@ProductID"].Value = prodID;

                da.SelectCommand = cmd;
                da.Fill(dt);

                result = dt.Rows.Count;
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return result;
        }

        public static void deductStock(string productID, int quantity)
        {
            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "deductStock";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 10));
                cmd.Parameters["@ProductID"].Value = productID;

                cmd.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
                cmd.Parameters["@Quantity"].Value = quantity;

                cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

        }

        public static void combineRedundantProduct(string orderID, string prodID, int quantity, double price)
        {
            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "combineRedundantProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.VarChar, 20));
                cmd.Parameters["@OrderID"].Value = orderID;

                cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 10));
                cmd.Parameters["@ProductID"].Value = prodID;

                cmd.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
                cmd.Parameters["@Quantity"].Value = quantity;

                cmd.Parameters.Add(new SqlParameter("@Price", SqlDbType.Decimal));
                cmd.Parameters["@Price"].Value = price;

                cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }
        }

        public static DataTable getSubTotal(string orderID)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "getSubTotal";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.VarChar, 20));
                cmd.Parameters["@OrderID"].Value = orderID;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return dt;
        }

        public static void DeleteProduct(int tempID)
        {
            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "DeleteProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                cmd.Parameters["@ID"].Value = tempID;

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }
            finally
            {
                if (con != null)
                { con.Close(); }
            }
        }

        public static void UpdateOrderTotalPrice(string orderID, double totalPrice)
        {
            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "UpdateOrderTotalPrice";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.VarChar, 20));
                cmd.Parameters["@OrderID"].Value = orderID;

                cmd.Parameters.Add(new SqlParameter("@totalPrice", SqlDbType.Decimal));
                cmd.Parameters["@totalPrice"].Value = totalPrice;

                cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }
        }

        public static int updateVendorProduct(string ProductName, int ID, decimal productPrice, string ImagePath, string Category, int Quantity, int SafeLevel, decimal Cost, string Description)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "updateVendorProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ProductName", SqlDbType.VarChar, 50));
                cmd.Parameters["@ProductName"].Value = ProductName;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                cmd.Parameters["@ID"].Value = ID;

                cmd.Parameters.Add(new SqlParameter("@productPrice", SqlDbType.Decimal));
                cmd.Parameters["@productPrice"].Value = productPrice;

                cmd.Parameters.Add(new SqlParameter("@ImagePath", SqlDbType.VarChar, 100));
                cmd.Parameters["@ImagePath"].Value = ImagePath;

                cmd.Parameters.Add(new SqlParameter("@Category", SqlDbType.VarChar, 50));
                cmd.Parameters["@Category"].Value = Category;

                cmd.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
                cmd.Parameters["@Quantity"].Value = Quantity;

                cmd.Parameters.Add(new SqlParameter("@SafeLevel", SqlDbType.Int));
                cmd.Parameters["@SafeLevel"].Value = SafeLevel;

                cmd.Parameters.Add(new SqlParameter("@Cost", SqlDbType.Decimal));
                cmd.Parameters["@Cost"].Value = Cost;

                cmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 2000));
                cmd.Parameters["@Description"].Value = Description;

                result = cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return result;
        }

        public static int addNewProduct(string productID, string productName, double price, string path, string category, string name, int quantity, int SafeLevel, decimal Cost, string Description)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "addProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 10));
                cmd.Parameters["@ProductID"].Value = productID;

                cmd.Parameters.Add(new SqlParameter("@ProductName", SqlDbType.VarChar, 50));
                cmd.Parameters["@ProductName"].Value = productName;

                cmd.Parameters.Add(new SqlParameter("@Price", SqlDbType.Decimal));
                cmd.Parameters["@Price"].Value = price;

                cmd.Parameters.Add(new SqlParameter("@Path", SqlDbType.VarChar, 100));
                cmd.Parameters["@Path"].Value = path;

                cmd.Parameters.Add(new SqlParameter("@Category", SqlDbType.VarChar, 20));
                cmd.Parameters["@Category"].Value = category;

                cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 50));
                cmd.Parameters["@name"].Value = name;

                cmd.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
                cmd.Parameters["@Quantity"].Value = quantity;

                cmd.Parameters.Add(new SqlParameter("@SafeLevel", SqlDbType.Int));
                cmd.Parameters["@SafeLevel"].Value = SafeLevel;

                cmd.Parameters.Add(new SqlParameter("@Cost", SqlDbType.Decimal));
                cmd.Parameters["@Cost"].Value = Cost;

                cmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 2000));
                cmd.Parameters["@Description"].Value = Description;

                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return result;
        }

        public static DataTable bindProductCategory()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listProductCategory";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable checkStockLevel(string productID)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "checkStockLevel";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 10));
                cmd.Parameters["@ProductID"].Value = productID;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable displayProductDetail(string productID)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "DisplayPicture";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 10));
                cmd.Parameters["@ProductID"].Value = productID;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable checkVendorCode(string vendorCode)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "checkVendorCode";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@VendorCode", SqlDbType.VarChar, 4));
                cmd.Parameters["@VendorCode"].Value = vendorCode;

                da.SelectCommand = cmd;
                da.Fill(dt);

            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return dt;
        }

        public static string getVendorCode(string vendor)
        {
            string vendorCode = "";

            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "showMemberDetails";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = vendor;

                da.SelectCommand = cmd;
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    vendorCode = dt.Rows[0][16].ToString();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return vendorCode;
        }

        public static string getLatestProductID(string vendor, string category)
        {
            string ProductID = "";

            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "getLatestProductID";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Vendor", SqlDbType.VarChar, 50));
                cmd.Parameters["@Vendor"].Value = vendor;

                cmd.Parameters.Add(new SqlParameter("@Category", SqlDbType.VarChar, 20));
                cmd.Parameters["@Category"].Value = category;

                da.SelectCommand = cmd;
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    ProductID = dt.Rows[0][0].ToString();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return ProductID;
        }

        public static DataTable listVendorLogo()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listVendor";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static string getProductIDByID(int ID)
        {
            string productID = "";

            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "getProductIDByID";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                cmd.Parameters["@ID"].Value = ID;

                da.SelectCommand = cmd;
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    productID = dt.Rows[0][0].ToString();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return productID;
        }

        public static DataTable DisplayPictureForVendor(string ID)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "DisplayPictureForVendor";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.VarChar, 20));
                cmd.Parameters["@ID"].Value = ID;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }
    }
}